Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What are the advantages of stored procedures?
Following are the advantages of stored procedures:
Since stored procedures are compiled and stored, whenever you call a procedure the response is quick.
you can group all the required SQL statements in a procedure and execute them at once.
Since procedures are stored on the database server which is faster than client. You can execute all the complicated quires using it, which will be faster.
Using procedures, you can avoid repetition of code moreover with these you can use additional SQL functionalities like calling stored functions.
Once you compile a stored procedure you can use it in any number of applications. If any changes are needed you can just change the procedures without touching the application code.
You can call PL/SQL stored procedures from Java and Java Stored procedures from PL/SQL.
Example
Assume we have created a table named Employees with the following description:
+----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | Name | varchar(255) | YES | | NULL | | | Salary | int(11) | NO | | NULL | | | Location | varchar(255) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+
Assume we have a procedure named myProcedure which inserts data into the Employees table as:
Create procedure myProcedure (IN name VARCHAR(30), IN sal INT, IN loc VARCHAR(45)) -> BEGIN -> INSERT INTO Employees(Name, Salary, Location) VALUES (name, sal, loc); -> END //
Following JDBC program calls the above mentioned Java stored procedure.
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Example {
public static void main(String args[]) throws SQLException {
//Registering the Driver
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//Getting the connection
String mysqlUrl = "jdbc:mysql://localhost/testdb";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
System.out.println("Connection established......");
//Preparing a CallableStatement
CallableStatement cstmt = con.prepareCall("{call myProcedure(?, ?, ?)}");
cstmt.setString(1, "Amit");
cstmt.setInt(2, 3000);
cstmt.setString(3, "Hyderabad");
cstmt.execute();
cstmt.setString(1, "Kalyan");
cstmt.setInt(2, 4000);
cstmt.setString(3, "Vishakhapatnam");
cstmt.execute();
}
}Output
Connection established......
If you verify the contents the Employees table, you can find the newly added rows as shown below:
+-----------+--------+----------------+ | Name | Salary | Location | +-----------+--------+----------------+ | Amit | 3000 | Hyderabad | | Kalyan | 4000 | Vishakhapatnam | +-----------+--------+----------------+
3K+ Views
